﻿using QuanLyGiaoVuDAL.ADOWrapper;
using QuanLyGiaoVuDAL.DTO;
using QuanLyGiaoVuDAL.Mapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace QuanLyGiaoVuDAL.Repositories.DatabaseRepositories
{
    public class DangKyGioDayRepository : IDangKyGioDayRepository
    {
        public DTO.DangKyGioDayDTO GetDangKyGioDayById(Guid id)
        {
            var maThongTinGiangDayParameter = DBParameterFactory.Create("MaThongTinGiangDay", id);
            var giangVienIdParameter = DBParameterFactory.Create("MaGiangVien", SqlDbType.UniqueIdentifier, DBNull.Value);
            var maHocKyParameter = DBParameterFactory.Create("MaHocKy", SqlDbType.UniqueIdentifier, DBNull.Value);
            var maLopMonHocParameter = DBParameterFactory.Create("MaLopMonHoc", SqlDbType.UniqueIdentifier, DBNull.Value);
            var maTietDauParameter = DBParameterFactory.Create("MaTietDau", SqlDbType.Int, DBNull.Value);
            var maTietSauParameter = DBParameterFactory.Create("MaTietSau", SqlDbType.Int, DBNull.Value);
            var thuParameter = DBParameterFactory.Create("Thu", SqlDbType.SmallInt, DBNull.Value);
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_THONGTINDANGKYGIODAYSelect]", maThongTinGiangDayParameter, giangVienIdParameter, maHocKyParameter, maLopMonHocParameter, maTietDauParameter, maTietSauParameter, thuParameter);
            var dataRow = dbResult.Tables[0].Rows[0];
            return dataRow.ToDangKyGioDayDTO();
        }

        public IEnumerable<DTO.DangKyGioDayDTO> GetAllDangKyGioday(Guid? giangVienId, Guid? lopMonHocId, int? tietDauId, int? tietSauId, int? thu, Guid? hocKyId)
        {
            List<DangKyGioDayDTO> lopMonHocList = new List<DangKyGioDayDTO>();
            var maThongTinGiangDayParameter = DBParameterFactory.Create("MaThongTinGiangDay", SqlDbType.UniqueIdentifier, DBNull.Value);
            var giangVienIdParameter = DBParameterFactory.Create("MaGiangVien", giangVienId);
            if (giangVienId == null)
                giangVienIdParameter = DBParameterFactory.Create("MaGiangVien", SqlDbType.UniqueIdentifier, DBNull.Value);
            var maHocKyParameter = DBParameterFactory.Create("MaHocKy", hocKyId);
            if (hocKyId == null)
                maHocKyParameter = DBParameterFactory.Create("MaHocKy", SqlDbType.UniqueIdentifier, DBNull.Value);
            var lopMonhocParameter = DBParameterFactory.Create("MaLopMonHoc", lopMonHocId);
            if (lopMonHocId == null)
                lopMonhocParameter = DBParameterFactory.Create("MaLopMonHoc", SqlDbType.UniqueIdentifier, DBNull.Value);
            var tietDauParameter = tietDauId != null ? DBParameterFactory.Create("MaTietDau",tietDauId):DBParameterFactory.Create("MaTietDau", SqlDbType.Int, DBNull.Value);
            var tietSauParameter = tietSauId != null ? DBParameterFactory.Create("MaTietSau", tietSauId):DBParameterFactory.Create("MaTietSau", SqlDbType.Int, DBNull.Value);
            var thuParameter = DBParameterFactory.Create("Thu", SqlDbType.SmallInt, thu);
            if (thu == null)
                thuParameter = DBParameterFactory.Create("Thu", SqlDbType.SmallInt, DBNull.Value);
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_THONGTINDANGKYGIODAYSelect]", maThongTinGiangDayParameter, giangVienIdParameter, maHocKyParameter, lopMonhocParameter, tietDauParameter, tietSauParameter, thuParameter);
            
            var lopMonHocTable = dbResult.Tables[0].Rows;
            foreach (DataRow dataRow in lopMonHocTable)
            {
                lopMonHocList.Add(dataRow.ToDangKyGioDayDTO());
            }
            return lopMonHocList.AsEnumerable();
        }

        public DTO.DangKyGioDayDTO InsertDangKyGioDay(DTO.DangKyGioDayDTO dangKyGioDayDTO)
        {
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_THONGTINDANGKYGIODAYInsert]", GetParameterArray(dangKyGioDayDTO));
            var dataRow = dbResult.Tables[0].Rows[0];
            return dataRow.ToDangKyGioDayDTO();
        }

        public DTO.DangKyGioDayDTO UpdateDangKyGioDay(DTO.DangKyGioDayDTO dangKyGioDayDTO)
        {
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_THONGTINDANGKYGIODAYUpdate]", GetParameterArray(dangKyGioDayDTO));
            var dataRow = dbResult.Tables[0].Rows[0];
            return dataRow.ToDangKyGioDayDTO();
        }

        public bool DeleteDangKyGioDay(Guid id)
        {
            SqlParameter lopMonHocIdParam = DBParameterFactory.Create("MaThongTinGiangDay", id);
            return StoreProcedureExecutor.ExecuteNonQueryStoredProcedure("[dbo].[usp_THONGTINDANGKYGIODAYDelete]", lopMonHocIdParam);
        }

        #region Private Methods
        private SqlParameter[] GetParameterArray(DangKyGioDayDTO dangKyGioDayDTO)
        {
            SqlParameter[] parameters = new SqlParameter[11];
            parameters[0] = DBParameterFactory.Create("MaThongTinGiangDay", dangKyGioDayDTO.MaThongTinGiangDay);
            parameters[1] = DBParameterFactory.Create("MaGiangVien", dangKyGioDayDTO.GiangVienId);
            parameters[2] = DBParameterFactory.Create("MaLopMonHoc", dangKyGioDayDTO.MaLopMonHoc);
            parameters[3] = DBParameterFactory.Create("MaTietDau", dangKyGioDayDTO.MaTietDau);
            parameters[10] = DBParameterFactory.Create("MaTietSau", dangKyGioDayDTO.MaTietSau);
            parameters[4] = DBParameterFactory.Create("Thu", dangKyGioDayDTO.Thu);
            parameters[5] = DBParameterFactory.Create("MaHocKy", dangKyGioDayDTO.MaHocKy);
            parameters[6] = DBParameterFactory.Create("DoUuTien", dangKyGioDayDTO.DoUuTien);
            parameters[7] = DBParameterFactory.Create("DuocChon", SqlDbType.Bit, dangKyGioDayDTO.DuocChon);
            parameters[8] = DBParameterFactory.Create("Huy", SqlDbType.Bit, dangKyGioDayDTO.Huy);
            parameters[9] = DBParameterFactory.Create("GhiChu", SqlDbType.NVarChar, dangKyGioDayDTO.GhiChu);
            if (String.IsNullOrEmpty(dangKyGioDayDTO.GhiChu))
                parameters[9] = DBParameterFactory.Create("GhiChu", SqlDbType.NVarChar, DBNull.Value);
            return parameters;
        }
        #endregion


        public IEnumerable<DangKyGioDayDTO> GetThongTinDangKyGioDayByIdList(string idList)
        {
            List<DangKyGioDayDTO> dsDangKy = new List<DangKyGioDayDTO>();
            SqlParameter idListParam = DBParameterFactory.Create("IdList", SqlDbType.VarChar, idList);
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_ThongTinDangKyGioDaySelectByIdList]", idListParam);
            var dsDangKyTable = dbResult.Tables[0].Rows;
            foreach (DataRow dataRow in dsDangKyTable)
            {
                dsDangKy.Add(dataRow.ToDangKyGioDayDTO());
            }
            return dsDangKy.AsEnumerable();
        }
    }
}
